﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Windows.Input;
using System.Data.SqlClient;
using System.Windows.Data;
using System.Windows.Threading;
using System.Windows;
using SqlMiM.Common.ViewModel;
using SqlMiM.Common.Commands;
using SqlMiM.Common;
using SqlMiM.Common.Model;
using SqlMiM.Common.Core;
using System.Data;
using System.Net;
using System.Collections.Specialized;
using System.Data.OleDb;
using System.Net.Sockets;
using System.Collections;
using MvvmFoundation.Wpf;

namespace SqlMiM.Common.ViewModel
{
    public class ServerLoginViewModel : ObservableObject
    {

        public enum Mode
        {
            Normal,
            RequestLoadServers,
            ServersLoaded
        }

        private CollectionViewSource serverIdentitiesView;
        private bool isLoadedServers;
        private bool isLoadingServers;
        private bool noServersFound;
        private bool isConnecting;
        private bool isValid;
        private bool isSqlServerAuthenticationMode;
        private String userName;
        private String serverName;
        private String password;
        private Int32 connectionTimeOut;
        private BackgroundWorker loaderWorker;
        private RelayCommand validateCommand;
        private RelayCommand loadServersCommand;
        private Mode currentMode;
        private SqlConnectionStringBuilder sqlConnectionStringBuilder;
        private Server serverConnection;

        #region Output Properties

        /// <summary>
        /// Server when autenticated
        /// </summary>
        public Server ServerConnection
        {
            get
            {
                return this.serverConnection;
            }
        }

        /// <summary>
        /// SqlConnectionStringBuilder when authenticated
        /// </summary>
        public SqlConnectionStringBuilder SqlConnectionStringBuilder
        {
            get
            {
                return this.sqlConnectionStringBuilder;
            }
        }

        #endregion


        /// <summary>
        /// Constructor
        /// </summary>
        public ServerLoginViewModel()
        {
            InitializeViewModel();
            sqlConnectionStringBuilder = new SqlConnectionStringBuilder();
            serverIdentitiesView = new CollectionViewSource();


        }

        /// <summary>
        /// Init vars
        /// </summary>
        private void InitializeViewModel()
        {
            this.ServerName = @".\SQL2008";

            this.ConnectionTimeOut = 5;
            this.UserName = string.Empty;
            this.Password = string.Empty;
            this.IsSqlServerAuthenticationMode = false;
            this.CurrentMode = Mode.Normal;
            this.IsConnecting = false;
            this.IsLoadedServers = false;
            this.IsLoadingServers = false;
            this.NoServersFound = false;

            loaderWorker = new BackgroundWorker();

            loaderWorker.DoWork += (sender, e) =>
            {
                e.Result = ServerIdentityView.GetServerIdentities();

            };

            loaderWorker.RunWorkerCompleted += (sender, e) =>
            {
                // Loading is terminated
                this.IsLoadingServers = false;

                List<ServerIdentityView> lstServers = e.Result as List<ServerIdentityView>;

                if (lstServers != null)
                {
                    // There is servers
                    this.NoServersFound = false;
                    this.IsLoadedServers = true;
                }
                else
                {
                    // There is no servers
                    this.NoServersFound = true;
                    this.IsLoadedServers = false;
                }

                this.ServerIdentitiesView.Source = lstServers;

                // Sort by Name
                this.ServerIdentitiesView.SortDescriptions.Add(new SortDescription("Name", ListSortDirection.Ascending));

                // Must refresh because first clic on combobox dont give good CurrentItem and Position ...
                this.ServerIdentitiesView.View.MoveCurrentToFirst();

            };


        }


        #region Load command

        /// <summary>
        /// Returns a command that saves the customer.
        /// </summary>
        public ICommand LoadServersCommand
        {
            get
            {
                if (loadServersCommand == null)
                {
                    loadServersCommand = new RelayCommand(this.LoadServers);

                }
                return loadServersCommand;
            }
        }

        /// <summary>
        /// Load servers
        /// </summary>
        private void LoadServers()
        {
            // If we have already load server, dont do it again
            if (this.IsLoadedServers == true && this.NoServersFound == false)
                return;

            // Loading in progress
            this.IsLoadingServers = true;

            loaderWorker.RunWorkerAsync();

        }

        #endregion


        #region Properties



        /// <summary>
        /// Mode of loadings servers
        /// </summary>
        public Mode CurrentMode
        {
            get { return currentMode; }
            set
            {
                if ((this.currentMode != value))
                {
                    this.currentMode = value;
                    this.RaisePropertyChanged("CurrentMode");
                }

            }
        }

        /// <summary>
        /// Servers view
        /// </summary>
        public CollectionViewSource ServerIdentitiesView
        {
            get { return serverIdentitiesView; }
            set
            {
                if ((this.serverIdentitiesView != value))
                {
                    this.serverIdentitiesView = value;
                    this.RaisePropertyChanged("ServerIdentitiesView");
                }
            }
        }

        /// <summary>
        /// This String is bind to ComboboxItem Text. It's only Set by Binding (no Get on it)
        /// </summary>
        public String ServerName
        {
            get
            {
                return this.serverName;
            }
            set
            {
                if (this.serverName != value)
                {
                    this.serverName = value;
                    this.RaisePropertyChanged("ServerName");
                }
            }
        }

        /// <summary>
        /// Check if all servers are loaded
        /// </summary>
        public bool IsLoadedServers
        {
            get { return isLoadedServers; }
            set
            {
                if ((this.isLoadedServers != value))
                {
                    this.isLoadedServers = value;
                    this.RaisePropertyChanged("IsLoadedServers");
                }
            }
        }

        /// <summary>
        /// Check if all servers are loaded
        /// </summary>
        public bool IsLoadingServers
        {
            get { return isLoadingServers; }
            set
            {
                if ((this.isLoadingServers != value))
                {
                    this.isLoadingServers = value;
                    this.RaisePropertyChanged("IsLoadingServers");
                }
            }
        }

        /// <summary>
        /// Get if the view Is Valid (i.e. Authentication is succeedeed)
        /// </summary>
        public bool IsValid
        {
            get { return isValid; }
            set
            {
                if ((this.isValid != value))
                {
                    this.isValid = value;
                    this.RaisePropertyChanged("IsValid");
                }
            }
        }

        /// <summary>
        /// Check if all servers are loaded
        /// </summary>
        public bool NoServersFound
        {
            get { return noServersFound; }
            set
            {
                if ((this.noServersFound != value))
                {
                    this.noServersFound = value;
                    this.RaisePropertyChanged("NoServersFound");
                }
            }
        }

        /// <summary>
        /// Is connecting
        /// </summary>
        public bool IsConnecting
        {
            get { return isConnecting; }
            set
            {
                if ((this.isConnecting != value))
                {
                    this.isConnecting = value;
                    this.RaisePropertyChanged("IsConnecting");
                }

            }
        }

        /// <summary>
        /// Mode d'authentification
        /// </summary>
        public Boolean IsSqlServerAuthenticationMode
        {
            get
            {
                return this.isSqlServerAuthenticationMode;
            }
            set
            {
                if ((this.isSqlServerAuthenticationMode != value))
                {
                    this.isSqlServerAuthenticationMode = value;
                    this.RaisePropertyChanged("IsSqlServerAuthenticationMode");
                }
            }
        }

        /// <summary>
        /// Connection TimeOut
        /// </summary>
        public Int32 ConnectionTimeOut
        {
            get
            {
                return this.connectionTimeOut;
            }
            set
            {
                if ((this.connectionTimeOut != value))
                {
                    this.connectionTimeOut = value;
                    this.RaisePropertyChanged("ConnectionTimeOut");
                }
            }
        }

        /// <summary>
        /// Sql Server UserName
        /// </summary>
        public String UserName
        {
            get { return userName; }
            set
            {
                if (this.userName != value)
                {
                    this.userName = value;
                    this.RaisePropertyChanged("UserName");
                }
            }
        }

        /// <summary>
        /// Sql Server Password
        /// </summary>
        public String Password
        {
            get { return password; }
            set
            {
                if (this.password != value)
                {
                    this.password = value;
                    this.RaisePropertyChanged("Password");
                }
            }
        }

        #endregion


        #region ValidateCommand and ValidateCommand action


        public event EventHandler Validated = null;

        /// <summary>
        /// Returns a command that saves the customer.
        /// </summary>
        public ICommand ValidateCommand
        {
            get
            {
                if (validateCommand == null)
                {
                    validateCommand = new RelayCommand(this.Validate, this.CanValidate);

                }
                return validateCommand;
            }
        }

        /// <summary>
        /// Log In
        /// </summary>
        /// <returns></returns>
        private void Validate()
        {
            sqlConnectionStringBuilder.ApplicationName = "Sql MiM";
            sqlConnectionStringBuilder.ConnectTimeout = this.ConnectionTimeOut;
            sqlConnectionStringBuilder.DataSource = this.ServerName;
            sqlConnectionStringBuilder.InitialCatalog = "master";

            if (!this.IsSqlServerAuthenticationMode)
            {
                // Use Windows authentication
                sqlConnectionStringBuilder.IntegratedSecurity = true;
            }
            else
            {
                // Use SQL Server authentication
                sqlConnectionStringBuilder.IntegratedSecurity = false;
                sqlConnectionStringBuilder.UserID = this.UserName;
                sqlConnectionStringBuilder.Password = this.Password;
            }

            BackgroundWorker bg = new BackgroundWorker();
            bg.DoWork += new DoWorkEventHandler(internalValidate);

            this.IsConnecting = true;

            bg.RunWorkerCompleted += ValidateCompleted;

            bg.RunWorkerAsync(sqlConnectionStringBuilder.ToString());
        }

        /// <summary>
        /// End of authentication
        /// </summary>
        private void ValidateCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            this.IsConnecting = false;

            if (e.Error != null)
            {
                this.IsValid = false;
                MessageBox.Show(e.Error.Message);
                return;
            }

            this.serverConnection = e.Result as Server;

            this.IsValid = true;

            if (Validated != null) Validated(sender, e);
        }

        /// <summary>
        /// Internal authentication
        /// </summary>
        private void internalValidate(object sender, DoWorkEventArgs e)
        {

            SqlConnection connexion = new SqlConnection((String)e.Argument);

            try
            {
                e.Result = ServerServices.Current.GetServer(connexion, true);

            }
            finally
            {
                if (connexion != null && connexion.State != System.Data.ConnectionState.Closed)
                    connexion.Close();

                if (connexion != null)
                {
                    connexion.Dispose();
                    connexion = null;
                }
            }

        }

        /// <summary>
        /// Returns true if the customer is valid and can be saved.
        /// </summary>
        public bool CanValidate()
        {
            return !String.IsNullOrEmpty(this.serverName) && !IsConnecting;
        }

        #endregion


    }


    public class ServerIdentityView
    {

        /// <summary>
        /// Name
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// Server
        /// </summary>
        public string Server { get; set; }

        /// <summary>
        /// Instance
        /// </summary>
        public string Instance { get; set; }

        /// <summary>
        /// IsClustered
        /// </summary>
        public bool IsClustered { get; set; }

        /// <summary>
        /// IsLocal
        /// </summary>
        public bool IsLocal { get; set; }

        /// <summary>
        /// Version
        /// </summary>
        public string Version { get; set; }

        /// <summary>
        /// Get all servers which brodcast on network
        /// </summary>
        public static List<ServerIdentityView> GetServerIdentities()
        {

            List<ServerIdentityView> servers = null;

            var tt = SqlServerInfo.Seek();

            var dtLocal = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(true);

            if (dtLocal != null && dtLocal.Rows != null && dtLocal.Rows.Count > 0)
            {
                servers = new List<ServerIdentityView>();

                foreach (DataRow dr in dtLocal.Rows)
                {
                    ServerIdentityView server = new ServerIdentityView();
                    server.Instance = dr["Instance"] as String;
                    server.Name = dr["Name"] as String;
                    server.Server = dr["Server"] as String;
                    server.Version = dr["Version"] as String;
                    server.IsClustered = dr["IsClustered"] != System.DBNull.Value ? (bool)dr["IsClustered"] : false;
                    server.IsLocal = dr["IsLocal"] != System.DBNull.Value ? (bool)dr["IsLocal"] : false;
                    servers.Add(server);


                }
            }

            var dtAll = Microsoft.SqlServer.Management.Smo.SmoApplication.EnumAvailableSqlServers(false);

            if (dtAll != null && dtAll.Rows != null && dtAll.Rows.Count > 0)
            {
                if (servers == null)
                    servers = new List<ServerIdentityView>();

                foreach (DataRow dr in dtLocal.Rows)
                {
                    ServerIdentityView server = new ServerIdentityView();
                    server.Instance = dr["Instance"] as String;
                    server.Name = dr["Name"] as String;
                    server.Server = dr["Server"] as String;
                    server.Version = dr["Version"] as String;
                    server.IsClustered = dr["IsClustered"] != System.DBNull.Value ? (bool)dr["IsClustered"] : false;
                    server.IsLocal = dr["IsLocal"] != System.DBNull.Value ? (bool)dr["IsLocal"] : false;

                    if (servers.FirstOrDefault(siv => siv.Name.Equals(server.Name, StringComparison.InvariantCultureIgnoreCase)) == null)
                        servers.Add(server);
                }
            }

            return servers;
        }


    }


    /// <summary>
    /// Not used. Can replace SqlServer SMO Objects
    /// </summary>
    public class SqlServerInfo
    {
        #region Fields
        private string oServerName;
        private string oInstanceName;
        private bool oIsClustered;
        private string oVersion;
        private int otcpPort;
        private string oNp;
        private string oRpc;
        private IPAddress oIP;
        private StringCollection oCatalogs;
        private string oUserId;
        private string oPassword;
        private bool oIntegratedSecurity = true;
        private int oTimeOut = 2;
        #endregion

        #region Constructors
        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerInfo"/> class.
        /// </summary>
        private SqlServerInfo()
        {

        }

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerInfo"/> class.
        /// </summary>
        /// <param name="ip">The ip.</param>
        /// <param name="info">The info.</param>
        public SqlServerInfo(IPAddress ip, byte[] info)
            : this(ip, System.Text.ASCIIEncoding.ASCII.GetString(info, 3, BitConverter.ToInt16(info, 1)))
        { }

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlServerInfo"/> class.
        /// </summary>
        /// <param name="ip">The ip address.</param>
        /// <param name="info">The info.</param>
        public SqlServerInfo(IPAddress ip, string info)
        {
            oIP = ip;
            string[] nvs = info.Split(';');
            for (int i = 0; i < nvs.Length; i += 2)
            {
                switch (nvs[i].ToLower())
                {
                    case "servername":
                        this.oServerName = nvs[i + 1];
                        break;

                    case "instancename":
                        this.oInstanceName = nvs[i + 1];
                        break;

                    case "isclustered":
                        this.oIsClustered = (nvs[i + 1].ToLower() == "yes");   //bool.Parse(nvs[i+1]);
                        break;

                    case "version":
                        this.oVersion = nvs[i + 1];
                        break;

                    case "tcp":
                        this.otcpPort = int.Parse(nvs[i + 1]);
                        break;

                    case "np":
                        this.oNp = nvs[i + 1];
                        break;

                    case "rpc":
                        this.oRpc = nvs[i + 1];
                        break;

                }
            }
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the IP address.
        /// </summary>
        /// <value>The address.</value>
        /// <remarks>Presently, this is not implemented and will always return null,</remarks>
        public IPAddress Address
        {
            get
            {
                return oIP;
            }
        }
        /// <summary>
        /// Gets the name of the server.
        /// </summary>
        /// <value>The name of the server.</value>
        public string ServerName
        {
            get
            {
                return oServerName;
            }
        }

        /// <summary>
        /// Gets the name of the instance.
        /// </summary>
        /// <value>The name of the instance.</value>
        public string InstanceName
        {
            get
            {
                return oInstanceName;
            }
        }
        /// <summary>
        /// Gets a value indicating whether this instance is clustered.
        /// </summary>
        /// <value>
        /// 	<see langword="true"/> if this instance is clustered; otherwise, <see langword="false"/>.
        /// </value>
        public bool IsClustered
        {
            get
            {
                return oIsClustered;
            }
        }
        /// <summary>
        /// Gets the version.
        /// </summary>
        /// <value>The version.</value>
        public string Version
        {
            get
            {
                return oVersion;
            }
        }
        /// <summary>
        /// Gets the TCP port.
        /// </summary>
        /// <value>The TCP port.</value>
        public int TcpPort
        {
            get
            {
                return otcpPort;
            }
        }
        /// <summary>
        /// Gets the named pipe.
        /// </summary>
        /// <value>The named pipe.</value>
        public string NamedPipe
        {
            get
            {
                return oNp;
            }
        }

        /// <summary>
        /// Gets the catalogs.
        /// </summary>
        /// <value>The catalogs.</value>
        public StringCollection Catalogs
        {
            get
            {
                if (oCatalogs == null)
                {
                    oCatalogs = GetCatalogs();
                }
                return oCatalogs;
            }
        }

        /// <summary>
        /// Gets or sets the user id.
        /// </summary>
        /// <value>The user id.</value>
        public string UserId
        {
            get { return oUserId; }
            set
            {
                oUserId = value;
                oIntegratedSecurity = false;
            }
        }

        /// <summary>
        /// Gets or sets the password.
        /// </summary>
        /// <value>The password.</value>
        public string Password
        {
            get { return oPassword; }
            set
            {
                oPassword = value;
                oIntegratedSecurity = false;
            }
        }

        /// <summary>
        /// Gets or sets a value indicating whether [integrated security].
        /// </summary>
        /// <value>
        /// 	<see langword="true"/> if [integrated security]; otherwise, <see langword="false"/>.
        /// </value>
        public bool IntegratedSecurity
        {
            get { return oIntegratedSecurity; }
            set { oIntegratedSecurity = value; }
        }

        /// <summary>
        /// Gets or sets the time out.
        /// </summary>
        /// <value>The time out.</value>
        public int TimeOut
        {
            get { return oTimeOut; }
            set { oTimeOut = value; }
        }

        #endregion

        #region Public Methods
        /// <summary>
        /// Tests the connection.
        /// </summary>
        /// <returns></returns>
        public bool TestConnection()
        {
            OleDbConnection conn = this.GetConnection();
            bool success = false;
            try
            {
                conn.Open();
                conn.Close();
                success = true;
            }
            catch { }
            return success;
        }


        /// <summary>
        /// Returns a <see cref="T:System.String"/> that represents the current <see cref="T:System.Object"/>.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.String"/> that represents the current <see cref="T:System.Object"/>.
        /// </returns>
        public override string ToString()
        {
            if (this.InstanceName == null || this.InstanceName == "MSSQLSERVER")
                return this.ServerName;
            else
                return this.ServerName + "\\" + this.InstanceName;
        }
        #endregion

        #region Private Methods
        private StringCollection GetCatalogs()
        {
            StringCollection catalogs = new StringCollection();

            try
            {
                OleDbConnection myConnection = this.GetConnection();
                myConnection.Open();
                DataTable schemaTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs,
                    null);
                myConnection.Close();
                foreach (DataRow dr in schemaTable.Rows)
                {
                    catalogs.Add(dr[0] as string);
                }
            }
            catch (Exception ex)
            {
                //				System.Windows.Forms.MessageBox.Show(ex.Message);
            }
            return catalogs;
        }

        private OleDbConnection GetConnection()
        {
            string myConnString = this.IntegratedSecurity ?
                String.Format("Provider=SQLOLEDB;Data Source={0};Integrated Security=SSPI;Connect Timeout={1}", this, this.TimeOut)
                : String.Format("Provider=SQLOLEDB;Data Source={0};User Id={1};Password={2};Connect Timeout={3}",
                this, this.UserId, this.Password, this.TimeOut);

            return new OleDbConnection(myConnString);
        }

        #endregion

        #region Public Static Method - Seek
        /// <summary>
        /// Seeks SQL servers on this network.
        /// </summary>
        /// <returns>An array of SqlServerInfo objects describing Sql Servers on this network</returns>
        static public SqlServerInfo[] Seek()
        {
            Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);

            socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.Broadcast, 1);
            socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 3000);

            //  For .Net v 2.0 it's a bit simpler
            //  socket.EnableBroadcast = true;	// for .Net v2.0
            //  socket.ReceiveTimeout = 3000;	// for .Net v2.0

            ArrayList servers = new ArrayList();
            try
            {
                byte[] msg = new byte[] { 0x02 };
                IPEndPoint ep = new IPEndPoint(IPAddress.Broadcast, 1434);
                socket.SendTo(msg, ep);

                int cnt = 0;
                byte[] bytBuffer = new byte[102400];
                do
                {

                    cnt = socket.Receive(bytBuffer);
                    servers.Add(new SqlServerInfo(null, bytBuffer));
                    socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 300);
                } while (cnt != 0);
            }
            catch (SocketException socex)
            {
                const int WSAETIMEDOUT = 10060;		// Connection timed out. 
                const int WSAEHOSTUNREACH = 10065;	// No route to host. 

                // Re-throw if it's not a timeout.
                if (socex.ErrorCode == WSAETIMEDOUT || socex.ErrorCode == WSAEHOSTUNREACH)
                {
                    // DO nothing......
                }
                else
                {
                    //					Console.WriteLine("{0} {1}", socex.ErrorCode, socex.Message);
                    throw;
                }
            }
            finally
            {
                socket.Close();
            }

            // Copy from the untyped but expandable ArrayList, to a
            // type-safe but fixed array of SqlServerInfos.

            SqlServerInfo[] aServers = new SqlServerInfo[servers.Count];
            servers.CopyTo(aServers);
            return aServers;
        }
        #endregion

    }

}
